/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DBLag;
import ModelLag.Employee;
import java.sql.*;
import java.util.ArrayList;
/**
 *
 * @author Ronni
 */
public class DbEmployee implements IFDbEmployee {
    private Connection con;
    private static final String DB_NAME = "Employee";
    private PreparedStatement pStmtSelect;
    
    public DbEmployee() {
        this.con = DbConnection1.getInstance().getDBcon();
        pStmtSelect = null;
    }

//    @Override
//    public Employee findEmployee(String cprNo) {
//        PreparedStatement preStmnt = null;    
//        String query = "SELECT cprNo, fName, lName, address, Employee.zipcode, email, phoneNo, city "
//                         + "FROM Employee, ZipcodeCity "
//                         + "WHERE Employee.zipCode = ZipcodeCity.zipCode";
//                //"SELECT * FROM "+DB_NAME+" WHERE cprNo = ?";
//        Employee employeeObj = null;
//        ResultSet results;
//        try{    
//            preStmnt = con.prepareStatement(query);
//            preStmnt.setString(1,cprNo);                
//            results = preStmnt.executeQuery();
//            if(results.next()){
//                 employeeObj = buildEmployee(results);	
//            }
//            }catch(SQLException SQLe)
//            {
//                System.out.println("SQL Exception: " + SQLe);
//            }
//            catch(Exception e){
//                System.out.println("Fejl i findEmployee(): " + e.getMessage());
//                
//            }
//            
//            return employeeObj;
//    }

    @Override
    public ArrayList<Employee> searchEmployee(String searchString) {
        PreparedStatement preStmnt = null;          
        ArrayList<Employee> employeeList = new ArrayList<Employee>();
        ResultSet results;
        Employee employeeObj = null;
        try{    
        String query = "SELECT * FROM " + DB_NAME + ",zipCodeCity WHERE cprNo LIKE '%" +searchString+"%' OR fName LIKE '%" + searchString + "%' OR lName LIKE '%" + searchString + "%' OR adress LIKE '%" + searchString + "%' OR zipCode LIKE '%" + searchString + "%' OR email LIKE '%" + searchString + "%' OR phoneNo LIKE '%" + searchString + "%' AND Customer.zipCode=zipCodeCity.zipCode";     
        preStmnt = con.prepareStatement(query);
        results = preStmnt.executeQuery();
        while(results.next()){
            employeeObj = buildEmployee(results);
            employeeList.add(employeeObj);	
            }
            preStmnt.close();
            }catch(SQLException SQLe)
            {
                System.out.println("SQL Exception: " + SQLe);
            }
            catch(Exception e){
                System.out.println("Fejl i searchEmployee(): " + e.getMessage());
                
            }
            
            return employeeList;
    }

    @Override
    public ArrayList<Employee> getAllEmployees() {
        PreparedStatement preStmnt = null;          
        ArrayList<Employee> employeeList = new ArrayList<Employee>();
        ResultSet results;
        Employee employeeObj = null;
        try{
            String query = "SELECT cprNo, fName, lName, address, city, email, Employee.zipcode, phoneNo, active "
                         + "FROM Employee, ZipcodeCity "
                         + "WHERE Employee.zipCode = ZipcodeCity.zipCode";    
            preStmnt = con.prepareStatement(query);
            results = preStmnt.executeQuery();
            while(results.next()){
                 employeeObj = buildEmployee(results);
                 employeeList.add(employeeObj);	
            }
            preStmnt.close();
            }catch(SQLException SQLe)
            {
                System.out.println("SQL Exception in employee: " + SQLe);
            }
            catch(Exception e){
                System.out.println("Fejl i findAllEmployees(): " + e.getMessage());
                
            }
            
            return employeeList;
        
    }

    @Override
    public int insertEmployee(Employee employee) {
        int rc = -1;
        PreparedStatement preStmnt = null;
        String query = null;
        
        try
        {
                query = "INSERT INTO " +DB_NAME+ " VALUES(?,?,?,?,?,?,?,?)";
                preStmnt = con.prepareStatement(query);
                //System.out.println(query);
                preStmnt.setString(1, employee.getCprNo());
                preStmnt.setString(2, employee.getfName());
                preStmnt.setString(3, employee.getlName());
                preStmnt.setString(4, employee.getAddress());
                preStmnt.setString(5, employee.getZipCode());
                preStmnt.setString(6, employee.getEmail());                
                preStmnt.setString(7, employee.getPhoneNo());
                preStmnt.setInt(8, 1);
                rc = preStmnt.executeUpdate();   
                
        }
        catch(SQLException SQLe)
        {
            //returnerer SQL Exception
            System.out.println("SQLException in InsertEmployee " + SQLe.getMessage());
        }
        catch(Exception e)
        {
            //returnerer java exception
            System.out.println("Exception in InsertEmployee" + e.getMessage());
        }        
        return rc;
    }

    @Override
    public int updateEmployee(Employee employee) {
        int rc = -1;
        String query = null;
        try
        {
            query = "UPDATE " +DB_NAME+ " SET fName = ?,lName = ?,address = ?,zipCode = ?,"
            + "email = ?,phoneNo = ? WHERE cprNo = ?";
            PreparedStatement preStmnt = con.prepareStatement(query);
            preStmnt = con.prepareStatement(query);
            
            preStmnt.setString(1, employee.getfName());
            preStmnt.setString(2, employee.getlName());
            preStmnt.setString(3, employee.getAddress());
            preStmnt.setString(4, employee.getZipCode());
            preStmnt.setString(5, employee.getEmail());
            preStmnt.setString(6, employee.getPhoneNo());
            preStmnt.setString(7, employee.getCprNo());
            rc = preStmnt.executeUpdate();   
                
        }
        catch(SQLException SQLe)
        {
            //returnerer SQL Exception
            System.out.println("SQL Error: " + SQLe.getMessage());
        }
        catch(Exception e)
        {
            //returnerer java exception
            System.out.println("ExceptionE: " + e.getMessage());
        }        
        return rc;
    }

    public int toggleActive(String cprNo) throws SQLException
    {
        String query = null;
        int rc = -1;
        Employee empl = findEmployee(cprNo);
        try{
            query = "UPDATE " +DB_NAME+ " SET active = ? WHERE cprNo = ?";
            PreparedStatement preStmnt = con.prepareStatement(query);
            preStmnt = con.prepareStatement(query);
            
            if(empl.isActive())
            {
                preStmnt.setInt(1, 0);
            }else
            {
                preStmnt.setInt(1, 1);
            }
            preStmnt.setString(2, empl.getCprNo());
            rc = preStmnt.executeUpdate();   
        }catch(Exception e)
        {
            throw new SQLException(e);
        }        
        return -1;
    }
    @Override
    public int deleteEmployee(Employee employee) {

        int rc = -1;
        PreparedStatement preStmnt = null;
        String query = null;
        try{
            
                query = "DELETE FROM " +DB_NAME+ " WHERE cprNo = ?";
                preStmnt = con.prepareStatement(query);
                preStmnt.setString(1, employee.getCprNo());
                rc = preStmnt.executeUpdate();
                preStmnt.close();
                
        }catch(SQLException SQLe)
        {
            //returnerer SQL Exception
            System.out.println("SQL Fejl: " + SQLe.getMessage());
        }
        catch(Exception e)
        {
            //returnerer java exception
            System.out.println("Exception " + e.getMessage());
        }        
        return rc;
    }
    
    private Employee buildEmployee(ResultSet results) {
        Employee employeeObj = new Employee();
       
         try{
                        
                employeeObj.setCprNo(results.getString("cprNo"));
                employeeObj.setfName(results.getString("fName"));
                employeeObj.setlName(results.getString("lName"));
                employeeObj.setAddress(results.getString("address"));
                employeeObj.setZipCode(results.getString("zipCode"));
                employeeObj.setCity(results.getString("city"));
                employeeObj.setEmail(results.getString("email"));
                employeeObj.setPhoneNo(results.getString("phoneNo"));
                employeeObj.setActive(results.getBoolean("active"));
          }
         catch(Exception e)
         {
             System.out.println("error building employee object: " + e.getMessage());
         }
        
        return employeeObj;
    }
    
    @Override
    public Employee findEmployee(String cprNo)
    {
        if(pStmtSelect == null)
        {
            pStmtSelect = getPreparedStmt();
        }
        Employee employeeObj = null;
        ResultSet results = null;
        
        try
        {
            pStmtSelect.setString(1, cprNo);
            results = pStmtSelect.executeQuery();
            if(results.next())
            {
                employeeObj = buildEmployee(results);
            }
        }
        catch(SQLException sqlE)
        {
            System.out.println("SQL Error");
            System.out.println(sqlE.getMessage());
        }
        catch(Exception e)
        {
            System.out.println(e.getMessage());
        }
        
        return employeeObj;  
    }
    
    
    private PreparedStatement getPreparedStmt()
    {
        try
        {
            String query = "SELECT cprNo, fName, lName, address, Employee.zipcode, email, phoneNo,active, city "
                         + "FROM Employee, ZipcodeCity "
                         + "WHERE cprNo = ? AND Employee.zipCode = ZipcodeCity.zipCode";
            //System.out.println("Preparestatement " + query);
            pStmtSelect = con.prepareStatement(query);
            pStmtSelect.setQueryTimeout(5);
        }
        catch(SQLException sqlE)
        {
            System.out.println("SQL Error");
            System.out.println(sqlE.getMessage());
        }
        catch(Exception e)
        {
            System.out.println(e.getMessage());
        }
        return pStmtSelect;
    }
    
    
}
